<?php

/*
 * Main class file for FoxyBack
 *
 */

include_once('config.inc.php');
include_once('hook.class.inc.php');
include_once('notify.class.inc.php');

class FoxyBack {
	var $table = array();
	var $allowed_fields = array();

	function __construct() {
		global $modx;
		$table_prefix = 'foxyback_';

		$tables = array('address', 'customer', 'custom_field', 'status', 'status_log', 'transaction',
		 'transaction_detail', 'transaction_detail_option');

		foreach ($tables as $table)
			$this->table[$table] = $modx->getFullTableName($table_prefix.$table);

		// List out all valid fields for each table.
		// 'Required' fields cannot be blank on edit.
		// Also defined whether the field is 'Editable'.
		$this->allowed_fields['address'] = array(
			'address_id'			=> array('R' => 1, 'E' => 0),
			'customer_id'			=> array('R' => 1, 'E' => 0),
			'transaction_id'		=> array('R' => 0, 'E' => 0),
			'address_type'			=> array('R' => 1, 'E' => 0),
			'first_name' 			=> array('R' => 1, 'E' => 1),
			'last_name' 			=> array('R' => 1, 'E' => 1), 
			'company' 				=> array('R' => 0, 'E' => 1), 
			'address1' 				=> array('R' => 1, 'E' => 1), 
			'address2' 				=> array('R' => 0, 'E' => 1), 
			'city'	 				=> array('R' => 1, 'E' => 1),
			'state' 				=> array('R' => 1, 'E' => 1),
			'postal_code' 			=> array('R' => 0, 'E' => 1),
			'country' 				=> array('R' => 1, 'E' => 1),
			'phone' 				=> array('R' => 1, 'E' => 1),
			'service_description'	=> array('R' => 0, 'E' => 1), 
			'subtotal'	 			=> array('R' => 0, 'E' => 1),
			'tax_total' 			=> array('R' => 0, 'E' => 1),
			'shipping_total' 		=> array('R' => 0, 'E' => 1),
			'total' 				=> array('R' => 0, 'E' => 1),
			'address_name' 				=> array('R' => 0, 'E' => 1),
		);
		$this->allowed_fields['customer'] = array( 
			'customer_id'			=> array('R' => 1, 'E' => 0),
			'address_id'			=> array('R' => 1, 'E' => 0),
			'email'					=> array('R' => 1, 'E' => 1), 
			'ip_address'			=> array('R' => 0, 'E' => 0), 
			'password'				=> array('R' => 1, 'E' => 0), 
			'web_user_id'			=> array('R' => 0, 'E' => 1), 
		);
		$this->allowed_fields['custom_field'] = array( 
			'custom_field_id'		=> array('R' => 1, 'E' => 0),
			'customer_id'			=> array('R' => 1, 'E' => 0),
			'transaction_id'		=> array('R' => 1, 'E' => 0),
			'address_id'			=> array('R' => 0, 'E' => 0),
			'field_name' 			=> array('R' => 1, 'E' => 1), 
			'field_value' 			=> array('R' => 1, 'E' => 1), 
		);
		$this->allowed_fields['status'] = array( 
			'status_id'				=> array('R' => 1, 'E' => 0),
			'name'					=> array('R' => 1, 'E' => 1), 
			'description'			=> array('R' => 0, 'E' => 1), 
			'sequence_id'			=> array('R' => 1, 'E' => 1), 
			'active'				=> array('R' => 1, 'E' => 1), 
		);
		$this->allowed_fields['status_log'] = array( 
			'status_log_id'			=> array('R' => 1, 'E' => 0),
			'customer_id'			=> array('R' => 1, 'E' => 0),
			'transaction_id'		=> array('R' => 1, 'E' => 0),
			'status_id'				=> array('R' => 1, 'E' => 0),
			'add_date'				=> array('R' => 1, 'E' => 0), 
			'notes'					=> array('R' => 0, 'E' => 1), 
		);
		$this->allowed_fields['transaction'] = array(
			'transaction_id'			=> array('R' => 1, 'E' => 0),
			'customer_id'				=> array('R' => 1, 'E' => 0),
			'transaction_date'			=> array('R' => 1, 'E' => 1), 
			'purchase_order'			=> array('R' => 0, 'E' => 1), 
			'product_total'				=> array('R' => 1, 'E' => 1), 
			'tax_total'					=> array('R' => 1, 'E' => 1), 
			'shipping_total'			=> array('R' => 1, 'E' => 1), 
			'order_total'				=> array('R' => 1, 'E' => 1), 
			'order_status_id'			=> array('R' => 1, 'E' => 1),
			'flagged'					=> array('R' => 1, 'E' => 1),
			'notes'						=> array('R' => 0, 'E' => 1),
			'rma_number' 				=> array('R' => 0, 'E' => 1),
			'ship_tracking_number' 		=> array('R' => 0, 'E' => 1),
			'ship_reference_number'		=> array('R' => 0, 'E' => 1),
			'ship_date'					=> array('R' => 0, 'E' => 1),
			'ship_method'				=> array('R' => 0, 'E' => 1),
		);
		$this->allowed_fields['transaction_detail'] = array(
			'transaction_detail_id' 	=> array('R' => 1, 'E' => 0),
			'transaction_id'			=> array('R' => 1, 'E' => 0),
			'product_name'				=> array('R' => 0, 'E' => 1), 
			'product_price'				=> array('R' => 0, 'E' => 1), 
			'product_quantity'			=> array('R' => 0, 'E' => 1), 
			'product_weight'			=> array('R' => 0, 'E' => 1), 
			'product_code'				=> array('R' => 0, 'E' => 1), 
			'product_delivery_type'		=> array('R' => 1, 'E' => 1), 
			'category_description'		=> array('R' => 1, 'E' => 1), 
			'category_code'				=> array('R' => 1, 'E' => 1), 
			'subscription_frequency'	=> array('R' => 0, 'E' => 1), 
			'subscription_startdate'	=> array('R' => 0, 'E' => 1), 
			'next_transaction_date'		=> array('R' => 0, 'E' => 1), 
			'shipto'					=> array('R' => 0, 'E' => 1),
		);
		$this->allowed_fields['transaction_detail_option'] = array(
			'transaction_detail_option_id'	=> array('R' => 1, 'E' => 0),
			'transaction_detail_id'			=> array('R' => 1, 'E' => 0),
			'transaction_id'				=> array('R' => 1, 'E' => 0),
			'product_option_name'			=> array('R' => 1, 'E' => 1), 
			'product_option_value'			=> array('R' => 1, 'E' => 1), 
			'price_mod'						=> array('R' => 0, 'E' => 1), 
			'weight_mod'					=> array('R' => 0, 'E' => 1),  
		);

		if (!$this->isInstalled()) {
			$this->firstRun($table_prefix);
		}
	}

	function isInstalled() {
		global $modx;

		return mysql_num_rows($modx->db->query('SHOW TABLES LIKE "%foxyback%"')) != 0;
	}

	// Create the db tables if necessary
	function firstRun($table_prefix) {
		include 'includes/foxyback.tables.php';
die("installed.");
	}

	// =================================================================
	// View functions.
	// =================================================================

	// Look up a status based on its name.
	function getStatusIdByName($status_name) {
		global $modx;
		$status_name = $modx->db->escape($status_name);
		$table = $this->table['status'];
		$res = $modx->db->query("SELECT `status_id` FROM $table WHERE `name` = '$status_name'");
		$row = $modx->db->getRow($res);
		
		if (!isset($row['status_id'])) {
			// Are there any statuses defined?
			$status_count = intval($modx->db->getValue("SELECT count(*) FROM $table WHERE 1"));
			if ($status_count <= 0) {
				// Empty table!! Add the default statuses.
				$status_array = array('New', 'Processed', 'Backordered', 'Failed', 'Fraud', 'Cancelled', 'Shipped', 'Return Pending', 'Return Complete');

				$i = 1;
				foreach ($status_array as $status) {
					$fields = array('name' => $status, 'sequence_id' => $i);
					$insert_id = $modx->db->insert($fields, $table);
					$i++;
				}

				// Try to find the default status again.
				$res = $modx->db->query("SELECT `status_id` FROM $table WHERE `name` = '$status_name'");
				$row = $modx->db->getRow($res);
			}
		}

		if (!isset($row['status_id'])) {
			$this->error_info = 'Error, that status is not defined';
			return NULL;
		}

		return $row['status_id'];
	}
	// end of getStatusIdByName

	// Look up a customer based on their email address.
	function getCustomerViewOnEmail($email) {
		global $modx;
		if ($email == '') {
			$this->error_info = 'Missing required parameter (email)';
			return NULL;
		}
		$email = $modx->db->escape($email);
		$result = $modx->db->select('*', $this->table['customer'], "email = '$email'");
		$customer_view = $modx->db->getRow($result);
		return $customer_view;
	}

	// View an address.
	function getAddressView($address_id) {
		global $modx;
		$address_id = $modx->db->escape($address_id);
		$result = $modx->db->select('*', $this->table['address'], "address_id = '$address_id'");
		$address_view = $modx->db->getRow($result);
		return $address_view;
	}

	// View the customer.
	function getCustomerView($customer_id) {
		global $modx;
		$customer_id = $modx->db->escape($customer_id);
		$result = $modx->db->select('*', $this->table['customer'], "customer_id = '$customer_id'");
		$customer_view = $modx->db->getRow($result);
		return $customer_view;
	}

	// View a single transaction. 
	// Note that this is just the basic view; use getTransactionList to view ALL data associated with 
	// the transaction.
	function getTransactionView($transaction_id) {
		global $modx;
		$transaction_id = $modx->db->escape($transaction_id);
		$result = $modx->db->select('*', $this->table['transaction'], "transaction_id = '$transaction_id'");
		$transaction_view = $modx->db->getRow($result);
		return $transaction_view;
	}

	function getTransactionDetailView($transaction_id) {
		global $modx;
		$result = $modx->db->select('*',
		 $this->table['transaction_detail'].' `t` JOIN '.
		 $this->table['transaction_detail_option'].' `o` ON '.
		 'o.transaction_detail_id = t.transaction_detail_id',
		 "t.transaction_id = '$transaction_id'");

		return $modx->db->makeArray($result);
	}

	
	// =================================================================
	// List functions.
	// =================================================================

	// Return an array of addresses that match the given criteria.
	function getAddressList($customer_id, $transaction_id=NULL, $address_type=NULL) {
		global $modx;

		$customer_id = $modx->db->escape($customer_id);
		$transaction_id = $modx->db->escape($transaction_id);
		$address_type = $modx->db->escape($address_type);

		// Build the where clause.
		$where_clause = "customer_id = '$customer_id'";
		if ($transaction_id != '') {
			$where_clause .= " AND transaction_id = '$transaction_id'";
		}
		if ($address_type != '') {
			$where_clause .= " AND address_type = '$address_type'";
		}

		// Get the list.
		$result = $modx->db->select('*', $this->table['address'], $where_clause);
		$address_list = $modx->db->makeArray($result);
		return $address_list;
	}

	// Return an array of all the statuses, keyed by id.
	function getStatusList($include_inactive=false) {
		global $modx;
		$table = $this->table['status'];

		// Include active statuses only unless otherwise requested.
		$where = "`active` = 'YES'";
		if ($include_inactive == true) {
			$where = '1';
		}

		$result = $modx->db->query("SELECT * FROM $table WHERE $where ORDER BY `sequence_id` ASC, `status_id` ASC");
		$status_list = $modx->db->makeArray($result);
		return $status_list;
	}

	// Return an array of transactions based on the given search criteria.
	function getTransactionList($search_array=array(), $sort_by='transaction_date DESC', $start=0, $many='100') {
		global $modx;

		// The search array contains all necessary search parameters.
		// The filter determines what transactions should be included in the search.
		$filter = $search_array['filter'];

		// This value is searched against multiple columns.
		$global_search = $search_array['global_search'];

		// TODO: search_array can also contain a custom where clause.

		// Process the filter parameter to construct the where clause..
		$where_clause = '';
		if ($filter != '') {
			$where_clause = $this->processFilter($filter, $search_array['global_filter_delimiter'], $search_array['local_filter_delimiter']);
		}

		// Extract the full table names for easy reference.
		$address_tbl = $this->table['address'];
		$customer_tbl = $this->table['customer'];
		$custom_field_tbl = $this->table['custom_field'];
		$transaction_tbl = $this->table['transaction'];
		$status_log_tbl = $this->table['status_log'];

		if ($global_search != '') {
			// Process the search across as many columns as possible.
			$global_search = $modx->db->escape($global_search);

			if (preg_match('/^\s*"(.*)"\s*$/', $global_search, $matches)) {
				// Remove the quotes.
				$global_search = $matches[1];
			}
			else {
				// Search each word separately.
				$global_search = preg_replace("/\s+/", '%', $global_search);
			}

			$global_search = "'%$global_search%'";

			// Include all of the likely columns.
			$global_where = '';
			$global_where .= $transaction_tbl 			. '.transaction_id LIKE '.$global_search;
			$global_where .= ' OR '.$transaction_tbl 	. '.customer_id LIKE '.$global_search;
			$global_where .= ' OR '.$transaction_tbl 	. '.purchase_order LIKE '.$global_search;
			$global_where .= ' OR '.$transaction_tbl 	. '.notes LIKE '.$global_search;

			$global_where .= ' OR '.$customer_tbl	. '.email LIKE '.$global_search;
			$global_where .= ' OR '.$customer_tbl 	. '.ip_address LIKE '.$global_search;

			$global_where .= ' OR '.$address_tbl 	. '.first_name LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.last_name LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.company LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.address1 LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.address2 LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.city LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.state LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.postal_code LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.country LIKE '.$global_search;
			$global_where .= ' OR '.$address_tbl 	. '.phone LIKE '.$global_search;

			$where_clause .= ' AND ('.$global_where.')';
		}

		// Construct the 'what' clause.
		$what_clause = '';
		$what_clause .= $transaction_tbl . '.transaction_id, ';
		$what_clause .= $transaction_tbl . '.transaction_date, ';
		$what_clause .= $transaction_tbl . '.customer_id, ';
		$what_clause .= $transaction_tbl . '.purchase_order, ';
		$what_clause .= $transaction_tbl . '.product_total, ';
		$what_clause .= $transaction_tbl . '.tax_total, ';
		$what_clause .= $transaction_tbl . '.shipping_total, ';
		$what_clause .= $transaction_tbl . '.order_total, ';
		$what_clause .= $transaction_tbl . '.order_status_id, ';
		$what_clause .= $transaction_tbl . '.flagged, ';
		$what_clause .= $transaction_tbl . '.notes, ';
		$what_clause .= $transaction_tbl . '.rma_number, ';
		$what_clause .= $transaction_tbl . '.ship_tracking_number, ';
		$what_clause .= $transaction_tbl . '.ship_reference_number, ';
		$what_clause .= $transaction_tbl . '.ship_date, ';
		$what_clause .= $transaction_tbl . '.ship_method, ';
		$what_clause .= $customer_tbl . '.email, ';
		$what_clause .= $customer_tbl . '.ip_address, ';
		$what_clause .= $customer_tbl . '.password, ';
		$what_clause .= $customer_tbl . '.address_id, ';
		$what_clause .= $customer_tbl . '.web_user_id, ';
		$what_clause .= $address_tbl . '.first_name, ';
		$what_clause .= $address_tbl . '.last_name, ';
		$what_clause .= $address_tbl . '.company, ';
		$what_clause .= $address_tbl . '.address1, ';
		$what_clause .= $address_tbl . '.address2, ';
		$what_clause .= $address_tbl . '.city, ';
		$what_clause .= $address_tbl . '.state, ';
		$what_clause .= $address_tbl . '.postal_code, ';
		$what_clause .= $address_tbl . '.country, ';
		$what_clause .= $address_tbl . '.phone, ';

		// The what clause cannot have a trailing comma.
		$what_clause = preg_replace("/,\s*$/", "", $what_clause);

		$join_clause = '';
		$join_clause .= "LEFT JOIN $customer_tbl ON $transaction_tbl.`customer_id`=$customer_tbl.`customer_id` ";
		$join_clause .= "LEFT JOIN $address_tbl ON $customer_tbl.`address_id`=$address_tbl.`address_id` ";

		// Ensure that the 'where' contains something.
		if ($where_clause == '') {$where_clause = 1;}

		// The where clause cannot begin with an 'AND'.
		$where_clause = preg_replace("/^\s*AND/", "", $where_clause);

		// Determine the total number of entries matched by this query without a limit.
		$total_count_sql = "SELECT $what_clause FROM " . $this->table['transaction'] . " $join_clause WHERE $where_clause";
		$result = $modx->db->query($total_count_sql);
		$total_count = $modx->db->getRecordCount($result);

		$sort_by = $modx->db->escape($sort_by);
		$start = $modx->db->escape($start);
		$many = $modx->db->escape($many);

		// Build the final query.
		$sql = "SELECT $what_clause FROM " . $this->table['transaction'] . " $join_clause WHERE $where_clause ORDER BY $sort_by LIMIT $start, $many";

		$result = $modx->db->query($sql);
		$transaction_list = $modx->db->makeArray($result);

		// The list to return after processing.
		$final_transaction_list = array();
		
		// Get the list of statuses for display purposes.
		$status_list = $this->getStatusList();
		$status_list_by_id = array();
		foreach ($status_list as $status) {
			$status_list_by_id[$status['status_id']] = $status['name'];
		}

		// Add the additional data for each transaction.
		foreach ($transaction_list as $transaction) {
			$transaction_id = $transaction['transaction_id'];
			$customer_id = $transaction['customer_id'];

			$transaction['customer_name'] = $transaction['first_name'].' '.$transaction['last_name'];

			// Return the date and time separately to provide more flexibility.
			$transaction['transaction_datetime'] = $transaction['transaction_date'];
			list($transaction['transaction_date'], $transaction['transaction_time']) 
				= explode(' ', $transaction['transaction_date']);

			// Return the date and time separately to provide more flexibility.
			$transaction['ship_datetime'] = $transaction['ship_date'];
			list($transaction['ship_date'], $transaction['ship_time']) 
				= explode(' ', $transaction['ship_date']);

			// Get the name for the current status.
			$transaction['order_status'] = $status_list_by_id[$transaction['order_status_id']];

			// Get the shipping addresses.
			$sql = "SELECT `address_id`, `transaction_id`, `first_name`, `last_name`, `address1`, `address2`, 
				`city`, `state`, `postal_code`, `country`, `phone`, `company`, 
				`service_description`, `subtotal`, `tax_total`, `shipping_total`, `total`
				FROM $address_tbl 
				WHERE `customer_id`='$customer_id' AND `address_type`='SHIPPING' 
				AND `transaction_id`='$transaction_id'
				ORDER BY `address_id`
			";
			$result = $modx->db->query($sql);
			$address_data = $modx->db->makeArray($result);

			if (is_array($address_data) && count($address_data) > 0) {
				$final_address_data = array();

				// Look for custom fields.
				foreach ($address_data as $address) {
					$address_id = $address['address_id'];
					$sql = "SELECT `custom_field_id`, `transaction_id`, `field_name`, `field_value` 
						FROM ".$this->table['custom_field']." 
						WHERE `transaction_id`='$transaction_id' AND `address_id`='$address_id' 
						ORDER BY `field_name`
					";
					$result = $modx->db->query($sql);
					$custom_data = $modx->db->makeArray($result);

					if (is_array($custom_data) && count($custom_data) > 0) {
						$address['custom_fields'] = $custom_data;
					}

					array_push ($final_address_data, $address);
				}

				$transaction['shipto_addresses'] = $final_address_data;
			}

			// Look for transaction details.
			$sql = "SELECT `transaction_detail_id`, `transaction_id`, `product_name`, `product_price`, 
				`product_quantity`, `product_weight`, `product_code`, `subscription_frequency`, 
				`subscription_startdate`, `next_transaction_date`, `shipto`, `category_description`, 
				`category_code`, `product_delivery_type`
				FROM ".$this->table['transaction_detail']." 
				WHERE `transaction_id`='$transaction_id'
				ORDER BY `product_name`
			";
			$result = $modx->db->query($sql);
			$detail_data = $modx->db->makeArray($result);

			if (is_array($detail_data) && count($detail_data) > 0) {
				$final_detail_data = array();

				// Look for detail options.
				foreach ($detail_data as $detail) {
					$detail_id = $detail['transaction_detail_id'];
					$sql = "SELECT `transaction_detail_option_id`, `transaction_detail_id`, `transaction_id`, 
						`product_option_name`, `product_option_value`, `price_mod`, `weight_mod` 
						FROM ".$this->table['transaction_detail_option']." 
						WHERE `transaction_detail_id`='$detail_id' 
						ORDER BY `product_option_name`
					";
					$result = $modx->db->query($sql);
					$option_data = $modx->db->makeArray($result);

					if (is_array($option_data) && count($option_data) > 0) {
						$detail['detail_options'] = $option_data;
					}

					array_push ($final_detail_data, $detail);
				}

				$transaction['transaction_details'] = $final_detail_data;
			}

			// Look for custom fields.
			$sql = "SELECT `custom_field_id`, `transaction_id`, `field_name`, `field_value` 
				FROM ".$this->table['custom_field']." 
				WHERE `transaction_id`='$transaction_id' AND `address_id` IS NULL 
				ORDER BY `field_name`
			";
			$result = $modx->db->query($sql);
			$custom_data = $modx->db->makeArray($result);

			if (is_array($custom_data) && count($custom_data) > 0) {
				$transaction['custom_fields'] = $custom_data;
			}
			
			// Look for status log entries.
			$sql = "SELECT `status_log_id`, `transaction_id`, `status_id`, `add_date`, `notes` 
				FROM ".$this->table['status_log']." 
				WHERE `transaction_id`='$transaction_id'
				ORDER BY `add_date`
			";
			$result = $modx->db->query($sql);
			$status_log_data = $modx->db->makeArray($result);

			if (is_array($status_log_data) && count($status_log_data) > 0) {
				// Get the name of each status.
				for ($i = 0; $i < count($status_log_data); $i++) {
					$status_log_data[$i]['status'] = $status_list_by_id[$status_log_data[$i]['status_id']];
				}
				$transaction['status_logs'] = $status_log_data;
			}

			// Save the transaction.
			array_push ($final_transaction_list, $transaction);
		}

		// Return the list with some other helpful information.
		$list_to_return = array(
			'LIST' 		=> $final_transaction_list,
			'TOTAL' 	=> $total_count,
			'COUNT'		=> count($final_transaction_list),
			'START'		=> $start,
			'MANY'		=> $many,
		);

		return $list_to_return;
	}
	// end of getTransactionList

	// Return an array of details for a specific transaction.
	function getTransactionDetailList($transaction_id) {
		global $modx;

		$transaction_id = $modx->db->escape($transaction_id);

		// Build the where clause.
		$where_clause = "transaction_id = '$transaction_id'";

		// Get the list.
		$result = $modx->db->select('*', $this->table['transaction_detail'], $where_clause);
		$detail_list = $modx->db->makeArray($result);
		return $detail_list;
	}

	// Return an array of detail options for a specific transaction detail.
	function getTransactionDetailOptionList($transaction_detail_id) {
		global $modx;

		$transaction_detail_id = $modx->db->escape($transaction_detail_id);

		// Build the where clause.
		$where_clause = "transaction_detail_id = '$transaction_detail_id'";

		// Get the list.
		$result = $modx->db->select('*', $this->table['transaction_detail_option'], $where_clause);
		$option_list = $modx->db->makeArray($result);
		return $option_list;
	}

	// Process the filter parameter and return a valid mysql where clause.
	function processFilter($filter, $global_filter_delimiter='|', $local_filter_delimiter=',') {
		global $modx;

		// Ensure that default delimiters are set.
		if ($global_filter_delimiter == '') { $global_filter_delimiter = '|'; }
		if ($local_filter_delimiter == '') { $local_filter_delimiter = ','; }

		// filter = clause|clause|clause
		// clause = field,criterion,mode
		$where_clause = '';

		// These are based on the Ditto filter modes, except they are used to INCLUDE, 
		// not exclude, transactions.

		// Translate each mode into the appropriate mysql operator.
		$mode_array = array(
			1		=> '!=',
			2		=> '=',
			3		=> '<',
			4		=> '>',
			5		=> '<=',
			6		=> '>=',
			7		=> 'NOT LIKE',
			8		=> 'LIKE',
			'!='	=> '!=',
			'=='	=> '=',
			'<'		=> '<',
			'>'		=> '>',
			'<='	=> '<=',
			'>='	=> '>=',
		);

		// Split up the filter into individual clauses.
		$clause_array = explode($global_filter_delimiter, $filter);

		foreach ($clause_array as $clause) {
			list($field, $criterion, $mode) = explode($local_filter_delimiter, $clause);

			if ($field == '' || $criterion == '' || $mode == '') {
				continue;
			}

			// Determine the table for this field.
			$tbl_name = '';
			if (array_key_exists($field, $this->allowed_fields['transaction'])) {
				$tbl_name = $this->table['transaction'];
			}
			elseif (array_key_exists($field, $this->allowed_fields['customer'])) {
				$tbl_name = $this->table['customer'];
			}
			elseif (array_key_exists($field, $this->allowed_fields['address'])) {
				$tbl_name = $this->table['address'];
			}
			else {
				continue;
			}

			// Translate the mode into the appropriate mysql operator.
			$operator = $mode_array[$mode];

			$field = $modx->db->escape($field);
			$criterion = $modx->db->escape($criterion);
			$where_clause .= " AND $tbl_name.`$field` $operator '$criterion'";
		}

		return $where_clause;
	}
	// end of processFilter


	// =================================================================
	// Check if entities already exist in the database.
	// =================================================================

	function customerExists($customer_id) {
		global $modx;
		$customer_id = $modx->db->escape($customer_id);
		$table = $this->table['customer'];
		$sql = "SELECT COUNT(customer_id) AS `exists` FROM $table WHERE `customer_id` = '$customer_id'";
		$returnValue = intval($modx->db->getValue($sql));
		if ($returnValue > 0 ) { return true; }
		return false;
	}
	
	function transactionExists($trans_id) {
		global $modx;
		$trans_id = $modx->db->escape($trans_id);
		$table = $this->table['transaction'];
		$sql = "SELECT COUNT(transaction_id) AS `exists` FROM $table WHERE `transaction_id` = '$trans_id'";
		$returnValue = intval($modx->db->getValue($sql));
		if ($returnValue > 0 ) { return true; }
		return false;
	}


	// =================================================================
	// Functions to add new entries to the database.
	// =================================================================

	function addAddress($add_spec=array()) {
		global $modx;
		$add_spec = $this->sanitizeAddSpec('address', $add_spec);
		$insert_id = $modx->db->insert($add_spec, $this->table['address']);
		return $insert_id;
	}

	function addCustomField($add_spec=array()) {
		global $modx;
		$add_spec = $this->sanitizeAddSpec('custom_field', $add_spec);
		$insert_id = $modx->db->insert($add_spec, $this->table['custom_field']);

		// Process this event.
		$hook = new Hook;
		if (!$hook->ProcessEvent('ADD', 'custom_field', $insert_id, $add_spec)) {
      $this->error_info =
       'Error processing hook in addCustomField: '.$hook->error_info;
			$this->notifyError();
      return NULL;
		}
		return $insert_id;
	}

	function addCustomer($add_spec=array()) {
		global $modx;
		$add_spec = $this->sanitizeAddSpec('customer', $add_spec);
		$insert_id = $modx->db->insert($add_spec, $this->table['customer']);

		// Process this event.
		$hook = new Hook;
		if (!$hook->ProcessEvent('ADD', 'customer', $insert_id, $add_spec)) {
			$this->error_info = 'Error processing hook in addCustomer: '.$hook->error_info;
			$this->notifyError();
			return NULL;
		}

		return $insert_id;
	}

	function addStatusLog($add_spec=array()) {
		global $modx;
		$add_spec = $this->sanitizeAddSpec('status_log', $add_spec);
		$insert_id = $modx->db->insert($add_spec, $this->table['status_log']);
		return $insert_id;
	}

	function addTransaction($add_spec=array()) {
		global $modx;
		$add_spec = $this->sanitizeAddSpec('transaction', $add_spec);
		$insert_id = $modx->db->insert($add_spec, $this->table['transaction']);

		// Process this event.
		$hook = new Hook;
		if (!$hook->ProcessEvent('ADD', 'transaction', $insert_id, $add_spec)) {
			$this->error_info = 'Error processing hook in addTransaction: '.$hook->error_info;
			$this->notifyError();
			return NULL;
		}

		return $insert_id;
	}

	function addTransactionDetail($add_spec=array()) {
		global $modx;
		$add_spec = $this->sanitizeAddSpec('transaction_detail', $add_spec);
		$insert_id = $modx->db->insert($add_spec, $this->table['transaction_detail']);

		$hook = new Hook;
		if (!$hook->ProcessEvent('ADD', 'transaction_detail', $insert_id, $add_spec)) {
			$this->error_info = 'Error processing hook in addTransactionDetail: '.$hook->error_info;
			$this->notifyError();
			return NULL;
		}
		return $insert_id;
	}

	function addTransactionDetailOption($add_spec=array()) {
		global $modx;
		$add_spec = $this->sanitizeAddSpec('transaction_detail_option', $add_spec);
		$insert_id = $modx->db->insert($add_spec, $this->table['transaction_detail_option']);
		return $insert_id;
	}


	// =================================================================
	// Functions to modify existing entries in the database.
	// =================================================================

	// Edit the customer information. Passwords are automatically hashed with md5.
	function editCustomer($customer_id, $edit_spec=array(), $skip_hook=false) {
		global $modx;

		$customer_id = $modx->db->escape($customer_id);

		// Sanitize.
		$edit_spec = $this->sanitizeEditSpec('customer', $edit_spec);
		if (!$customer_id || count($edit_spec) == 0) {
			// Nothing to edit.
			return 1;
		}

		$update_id = $modx->db->update($edit_spec, $this->table['customer'], "customer_id = '$customer_id'");

		// Process this event unless it was requested to skip.
		if (!$skip_hook) {
			$hook = new Hook;
			if (!$hook->ProcessEvent('EDIT', 'customer', $customer_id, $edit_spec)) {
				$this->error_info = "Error processing hook in editCustomer ($customer_id): ".$hook->error_info;
				$this->notifyError();
				return NULL;
			}
		}

		return $update_id;
	}

	// Edit a transaction.
	// Note: if the order_status_id is being modified, it is recommended to include 'status_notes' 
	// that explain why the status is changing. This is inserted into the status log.
	function editTransaction($transaction_id, $edit_spec=array(), $skip_hook=false) {
		global $modx;

		$transaction_id = $modx->db->escape($transaction_id);

		// Determine if the status is changing.
		$status_changed = false;
		if (array_key_exists('order_status_id', $edit_spec)) {
			// View the transaction.
			$result = $modx->db->select('customer_id, order_status_id', $this->table['transaction'], "transaction_id = '$transaction_id'");
			$trans_view = $modx->db->getRow($result);

			if ($trans_view['order_status_id'] != $edit_spec['order_status_id']) {
				// The status is changing, so insert a status log entry.
				$status_changed = true;
				$add_spec = array(
					'transaction_id' => $transaction_id,
					'customer_id' => $trans_view['customer_id'],
					'status_id' => $edit_spec['order_status_id'],
					'add_date' => $this->formatMySQLDate(),
					'notes' => $edit_spec['status_notes'],
				);

				$add_spec = $this->dbEscapeArray($add_spec);
				$insert_id = $modx->db->insert($add_spec, $this->table['status_log']);
			}
		}

		// Sanitize.
		$edit_spec = $this->sanitizeEditSpec('transaction', $edit_spec);
		if (count($edit_spec) == 0) {
			// Nothing to edit.
			return 1;
		}

		// Edit the transaction.
		$update_id = $modx->db->update($edit_spec, $this->table['transaction'], "transaction_id = '$transaction_id'");

		// Process this event unless it was requested to skip.
		$hook = new Hook;
		if (!$skip_hook) {
			// Process this event.
			if (!$hook->ProcessEvent('EDIT', 'transaction', $transaction_id, $edit_spec)) {
				$this->error_info = "Error processing hook in editTransaction ($transaction_id): ".$hook->error_info;
				$this->notifyError();
				return NULL;
			}
		}

		// A hook is ALWAYS generated if the status changed.
		if ($status_changed) {
			if (!$hook->ProcessEvent('EDIT:order_status_id', 'transaction', $transaction_id, $edit_spec)) {
				$this->error_info = "Error processing 'status' hook in editTransaction ($transaction_id): ".$hook->error_info;
				$this->notifyError();
				return NULL;
			}
		}

		return $update_id;
	}
	// end of editTransaction

	// Edit a transaction detail, plus options.
	function editTransactionDetail($transaction_detail_id, $edit_spec=array()) {
		global $modx;

		$transaction_detail_id = $modx->db->escape($transaction_detail_id);

		// Save the options spec for later if it exists.
		$options_spec = '';
		if (array_key_exists('detail_options', $edit_spec) && is_array($edit_spec['detail_options'])) {
			$options_spec = $edit_spec['detail_options']; 
		}

		// Sanitize.
		$edit_spec = $this->sanitizeEditSpec('transaction_detail', $edit_spec);
		if (count($edit_spec) == 0) {
			// Nothing to edit.
			return 1;
		}

		// Edit the transaction.
		$update_id = $modx->db->update($edit_spec, $this->table['transaction_detail'], "transaction_detail_id = '$transaction_detail_id'");

		// Process any options.
		if (is_array($options_spec)) {
			foreach ($options_spec as $option_id => $option_spec) {
				$option_spec = $this->sanitizeEditSpec('transaction_detail_option', $option_spec);
				if (count($option_spec) == 0) {
					// Nothing to edit.
					continue;
				}

				// Edit the transaction.
				$option_update_id = $modx->db->update($option_spec, $this->table['transaction_detail_option'], "transaction_detail_option_id = '$option_id'");
			}
		}

		return $update_id;
	}
	// end of editTransactionDetail

	function editAddress($address_id, $edit_spec=array(), $skip_hook=false) {
		global $modx;
		
		$address_id = $modx->db->escape($address_id);
		
		// Sanitize.
		$edit_spec = $this->sanitizeEditSpec('address', $edit_spec);
		if (count($edit_spec) == 0) {
			// Nothing to edit.
			return NULL;
		}

		$update_id = $modx->db->update($edit_spec, $this->table['address'], "address_id = '$address_id'");

		// Process this event unless it was requested to skip.
		if (!$skip_hook) {
			$hook = new Hook;
			if (!$hook->ProcessEvent('EDIT', 'address', $address_id, $edit_spec)) {
				$this->error_info = "Error processing hook in editAddress ($address_id): ".$hook->error_info;
				$this->notifyError();
				return NULL;
			}
		}

		return $update_id;
	}
	
	function editStatusLog($status_log_id, $edit_spec=array()) {
		global $modx;
		
		$status_log_id = $modx->db->escape($status_log_id);
		
		// Sanitize.
		$edit_spec = $this->sanitizeEditSpec('status_log', $edit_spec);
		if (count($edit_spec) == 0) {
			// Nothing to edit.
			return 1;
		}

		$update_id = $modx->db->update($edit_spec, $this->table['status_log'], "status_log_id = '$status_log_id'");
		return $update_id;
	}


	// =================================================================
	// Helper functions.
	// =================================================================

	// Send an error email with the contents of the current error string.
	function notifyError() {
		$notify_obj = new Notify();
		$notify_obj->sendErrorEmail($this->error_info);
		return 1;
	}

	// Prepare a spec for sql insertion by removing any nonexistent fields. 
	// Db escape each value as well.
	function sanitizeAddSpec ($table_name, $add_spec=array()) {
		global $modx;
		// Verify the fields to add.
		foreach ($add_spec as $key => $value) {
			if ( !array_key_exists($key, $this->allowed_fields[$table_name])
				|| $this->allowed_fields[$table_name][$key]['R'] == 1 && $value == '' )
			{
				// Remove this field.
				unset($add_spec[$key]);
			}
			else {
				// Escape for the db.
				$add_spec[$key] = $modx->db->escape($value);
			}
		}

		// Return the sanitized spec.
		return $add_spec;
	}

	// Prepare a spec for sql update by removing any non-editable fields.
	// Required fields with no value are also removed.
	// Db escape each value as well.
	function sanitizeEditSpec ($table_name, $edit_spec=array(), $skip_escape=FALSE) {
		global $modx;
		// Verify the fields to edit.
		foreach ($edit_spec as $key => $value) {
			if ( !array_key_exists($key, $this->allowed_fields[$table_name])
				|| $this->allowed_fields[$table_name][$key]['E'] == 0 
				|| $this->allowed_fields[$table_name][$key]['R'] == 1 && $value == ''
			)
			{
				// Remove this field.
				unset($edit_spec[$key]);
			}
			elseif (!$skip_escape) {
				// Escape for the db.
				$edit_spec[$key] = $modx->db->escape($value);
			}
		}

		// Return the sanitized spec.
		return $edit_spec;
	}

	// Escape each value in the array for use in a db query.
	function dbEscapeArray($trans_spec=array()) {
		global $modx;

		foreach($trans_spec as $n=>$v) {
			$trans_spec[$n] = $modx->db->escape($v);
		}

		return $trans_spec;
	}

	// Return a timestamp formatted for a mysql datetime field.
	function formatMySQLDate($timestamp=NULL) {
		if (!isset($timestamp)) {
			$timestamp = time();
		}
		return date('Y-m-d H:i:s', $timestamp);
	}


} // end of class

?>
